package com.smart.custom;

import com.fr.base.FRContext;
import com.fr.file.DatasourceManager;
import com.fr.third.javassist.bytecode.annotation.BooleanMemberValue;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class OrderTrackingQuery extends DynamicQueryData {

    @Override
    public void initFeilds() {
        this.feildsHeard.clear();
        this.feildsHeard.add("订单列表id");
        this.feildsHeard.add("订单号");
        this.feildsHeard.add("区域组织");
        this.feildsHeard.add("客户");
        this.feildsHeard.add("最终客户");
        this.feildsHeard.add("销售员");
        this.feildsHeard.add("跟单人");
        this.feildsHeard.add("下单日期");
        this.feildsHeard.add("创建日期");
        this.feildsHeard.add("订单数量");
        this.feildsHeard.add("含税总金额");
        this.feildsHeard.add("不含税总金额");
        this.feildsHeard.add("币别");
        this.feildsHeard.add("审核人");
        this.feildsHeard.add("审核日期");
        this.feildsHeard.add("最晚交期");
        this.feildsHeard.add("订单类型");
        this.feildsHeard.add("销售年度");
        this.feildsHeard.add("销售季度");
        this.feildsHeard.add("状态");
        this.feildsHeard.add("出口代理");
        this.feildsHeard.add("完结日期");
        this.feildsHeard.add("纱需求用量");
        this.feildsHeard.add("计划采购数");
        this.feildsHeard.add("备纱可用数");
        this.feildsHeard.add("以下单总数");
        this.feildsHeard.add("未下单数");
        this.feildsHeard.add("坯纱可用量");
        this.feildsHeard.add("已消耗");
        this.feildsHeard.add("以下染纱数");
        this.feildsHeard.add("染纱产量");
        this.feildsHeard.add("织下单数");
        this.feildsHeard.add("织布产量");
        this.feildsHeard.add("染布下单数");
        this.feildsHeard.add("色布生产数");
        this.feildsHeard.add("印花下单数");
        this.feildsHeard.add("印花生产数");
        this.feildsHeard.add("色纱可用量");
        this.feildsHeard.add("坯布可用量");
        this.feildsHeard.add("色布可用量");
        this.feildsHeard.add("印花布可用量");
        this.feildsHeard.add("坯布销售出货数量");
        this.feildsHeard.add("色布出货单数量");
        this.feildsHeard.add("印花销售出货数量");
        this.feildsHeard.add("色布退货入库数量");
        this.feildsHeard.add("印花布退货入库数量");
}

    @Override
    public List<Object[]> queryData() {
        FRContext.getLogger().info("Start query" );
        ArrayList rowData=new ArrayList();
        String status= (String)this.paras.get("1");
        String sales_order_code=(String)this.paras.get("2");
        String name=(String)this.paras.get("3");

        String cons="";

        if(status.length()>0){cons+=" AND ot.status="+status+"";}
        else{cons+=" AND ot.status in (2,4,6)"+"";}
        if(sales_order_code.length()>0){cons+=" AND ot.sales_order_code like '%"+sales_order_code+"%'";}
        if(name.length()>0){cons+=" AND ta1.name like '%"+name+"%'";}
        String sql="SELECT \n" +
                "ot.id AS 订单列表id,\n" +
                "ot.sales_order_code AS 订单号, \n" +
                "ot.sales_organization_id AS 区域组织,\n" +
                "ot.customer_id AS 客户,\n" +
                "ot.end_customer_id AS 最终客户,\n" +
                "ot.salesperson_id AS 销售员,\n" +
                "ot.single_id AS 跟单人,\n" +
                "ot.order_date AS 下单日期,\n" +
                "ot.order_created_at AS 创建日期,\n" +
                "ot.qty_auxiliary AS 订单数量,\n" +
                "ot.money_with_tax AS\t含税总金额,\n" +
                "ot.amount AS\t不含税总金额,\n" +
                "c1.`name` AS\t币别,\n" +
                "ot.approve_user_id AS\t审核人,\n" +
                "ot.approve_date AS\t审核日期,\n" +
                "ot.end_delivery AS\t最晚交期,\n" +
                "ot.sales_type*1 AS\t订单类型,\n" +
                "ot.`annual_sale` AS\t销售年度,\n" +
                "ot.`quarterly_sale`*1 AS 销售季度,\n" +
                "ot.`status`*1 AS\t状态,\n" +
                "ot.abbreviation AS\t出口代理,\n" +
                "ot.finish_date AS\t完结日期,\n" +
                "otd.yarn_need_qty AS 纱需求用量,\n" +
                "otd.finished_qty AS 计划采购数,\n" +
                "otd.prepare_usable_qty AS 备纱可用数,\n" +
                "otd.purchase_qty AS 已下单总数,\n" +
                "otd.owe_order_qty AS 未下单数,\n" +
                "otd.yarn_usable_qty AS 坯纱可用量,\n" +
                "otd.yarn_used_qty AS 已消耗,\n" +
                "otd.dyeing_yarn_qty\t AS 已下染纱数,\n" +
                "otd.dyeing_produce_qty\t AS 染纱产量,\n" +
                "otd.knit_greige_qty\t AS 织下单数,\n" +
                "otd.knit_in_stock_qty\t AS 织布产量,\n" +
                "otd.dye_finished_qty\t AS 染布下单数,\n" +
                "otd.dye_in_stock_qty\t AS 色布生产数,\n" +
                "otd.print_finished_qty\t AS 印花下单数,\n" +
                "otd.print_in_stock_qty\t AS 印花生产数,\n" +
                "otd.dyeing_stock_qty\t AS 色纱可用量,\n" +
                "otd.knit_stock_qty\t AS 坯布可用量,\n" +
                "otd.dye_stock_qty\t AS 色布可用量,\n" +
                "otd.print_stock_qty\t AS 印花布可用量,\n" +
                "otd.knit_sales_out_qty\t AS 坯布销售出货数量,\n" +
                "otd.dye_sales_out_qty\t AS 色布出货单数量,\n" +
                "otd.print_sales_out_qty\t AS 印花销售出货数量,\n" +
                "otd.dye_return_in_qty\t AS 色布退货入库数量,\n" +
                "otd.print_return_in_qty\t AS 印花布退货入库数量\n" +
                "FROM order_tracking AS ot \n" +
                "LEFT JOIN order_tracking_detail otd ON(ot.sales_order_id=otd.sales_order_id)\n" +
                "LEFT JOIN (SELECT id,`name` FROM currencies WHERE deleted_at IS NULL) c1 ON(ot.currency_id=c1.id)\n" +
                "LEFT JOIN (SELECT FID,`name` FROM t_account WHERE FDeletedAt IS NULL ) ta1 ON(ot.salesperson_id=ta1.FID)\n" +
                "LEFT JOIN (SELECT id,abbreviation FROM company_accounts WHERE abbreviation IS NOT NULL AND deleted_at IS NULL )  ca1 ON(ot.customer_id=ca1.id)\n" +
                "WHERE 1=1 "+cons+"";
        com.fr.data.impl.Connection conn = DatasourceManager.getInstance().getConnection("JDBC3");
        try{
            Connection con = conn.createConnection();
            PreparedStatement stmt=con.prepareStatement(sql);
            ResultSet rs=stmt.executeQuery();

            Object[] objArray = null;
            int colNum=feildsHeard.size();

            while (rs.next()){
                objArray = new Object[feildsHeard.size()];
                for (int i = 0; i < colNum; i++) {
                    objArray[i] = rs.getObject(i + 1);
                    System.out.println("query+"+objArray[i]);
                }
                rowData.add(objArray);
            }

            rs.close();
            stmt.close();
            con.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        return rowData;
    }
}
